
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_List_stp_auth_staff2')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_List_stp_auth_staff2
GO

/****** Object:  StoredProcedure [dbo].[USP_List_stp_auth_staff2]    Script Date: 12/17/2010 01:31:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		JOE
-- Create date: 17:36 28/11/2010
-- [USP_List_stp_auth_staff2] 0,0,0,0,0,0,0
-- =============================================
CREATE PROCEDURE [dbo].[USP_List_stp_auth_staff2]  
	-- Add the parameters for the stored procedure here
	@staff_code int,
	@current_staff_code int,
	@department_code int,
	@group_code int,
	@main_system_code int,
	@menu_system_code int,
	@auth_staff_status int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

    -- Modified by Boonyarit Yingvoragan
    -- Modified date 26/10/2553
    declare @master_department_level as integer
	declare @table as table(department_code int)
	declare @level as int
	
	 
	-- if you want to select all department, use the staff code's department.
	if @department_code = 0
		select @department_code = department_code from staff where staff_code = @current_staff_code

	-- find out the department level of staff
	select @level = mas.master_department_level 
	from stp_department dep 
	inner join master_department mas on dep.master_department_code = mas.master_department_code
	inner join staff s on s.department_code = dep.department_code 
	where s.staff_code = @staff_code 
	
	-- if you are super admin, you can see every department.
	if @department_code = 0
	begin
		insert @table 
		select dep.department_code
		from stp_department dep 
		where department_code > 0
	end
	
	-- you are normal staff, you can see only your child department.
	if @department_code > 0
	begin
		insert @table select @department_code
		while @level < 10
		begin
			insert @table 
			select department_code 
			from stp_department 
			where department_parent in (select department_code from @table) 
			set @level = @level + 1
		end
	end


	-- =============================================
	-- Query data from authenticated group
	-- =============================================
	select	
	--	SeqNo = ROW_NUMBER() OVER (ORDER BY dept.department_code) ,
		dept.department_code,
			auth_staff.staff_code staff_code,
			fullname = isnull(s.staff_firstname,'') + ' ' + isnull(s.staff_lastname,''),
			g.group_user_name,
			dept.department_name,
			sms.main_system_code,
			smas.main_system_name,
			sms.menu_system_name,
			--ssm.sub_menu_name,
			--mt.[type_name],
			dept.department_parent,
			mas.master_department_level,
			auth_staff.auth_staff_status,
			dbo.[fn_GetAuthen](auth.department_code,1,ssm.menu_system_code,sms.main_system_code) as auth1,
			dbo.[fn_GetAuthen](auth.department_code,2,ssm.menu_system_code,sms.main_system_code) as auth2,
			dbo.[fn_GetAuthen](auth.department_code,3,ssm.menu_system_code,sms.main_system_code) as auth3,
			dbo.[fn_GetAuthen](auth.department_code,4,ssm.menu_system_code,sms.main_system_code) as auth4,
			dbo.[fn_GetAuthen](auth.department_code,5,ssm.menu_system_code,sms.main_system_code) as auth5,
			dbo.[fn_GetAuthen](auth.department_code,6,ssm.menu_system_code,sms.main_system_code) as auth6,
			dbo.[fn_GetAuthen](auth.department_code,7,ssm.menu_system_code,sms.main_system_code) as auth7,
			dbo.[fn_GetAuthen](auth.department_code,8,ssm.menu_system_code,sms.main_system_code) as auth8,
			dbo.[fn_GetAuthen](auth.department_code,9,ssm.menu_system_code,sms.main_system_code) as auth9,
			dbo.[fn_GetAuthen](auth.department_code,10,ssm.menu_system_code,sms.main_system_code) as auth10,
			dbo.[fn_GetAuthen](auth.department_code,11,ssm.menu_system_code,sms.main_system_code) as auth11,
			dbo.[fn_GetAuthen](auth.department_code,12,ssm.menu_system_code,sms.main_system_code) as auth12,
			dbo.fn_GetRootHirachy(dept.department_parent,1) as parentName1
			,dbo.fn_GetRootHirachy(dept.department_parent,2) as parentName2
			,dbo.fn_GetRootHirachy(dept.department_parent,3) as parentName3
			,dbo.fn_GetRootHirachy(dept.department_parent,4) as parentName4
			,dbo.fn_GetRootHirachy(dept.department_parent,5) as parentName5
			,dbo.fn_GetRootHirachy(dept.department_parent,6) as parentName6
			,dbo.fn_GetRootHirachy(dept.department_parent,7) as parentName7
			,dbo.fn_GetRootHirachy(dept.department_parent,8) as parentName8
			,dbo.fn_GetRootHirachy(dept.department_parent,9) as parentName9
			,dbo.fn_GetRootHirachy(dept.department_parent,10) as parentName10
			,dbo.fn_GetRootHirachy(dept.department_parent,11) as parentName11
			,dbo.fn_GetRootHirachy(dept.department_parent,12) as parentName12
			,dbo.fn_GetRootHirachy(dept.department_parent,13) as parentName13
			,dbo.fn_GetRootHirachy(dept.department_parent,14) as parentName14
			,dbo.fn_GetRootHirachy(dept.department_parent,15) as parentName15
  
	from	stp_auth_department as auth
	join	stp_department as dept on auth.department_code = dept.department_code
	join	master_department as mas on mas.master_department_code = dept.master_department_code 
	join	stp_auth_group auth_group on auth_group.auth_dep_code = auth.auth_department_code 
	join	group_user g on g.group_user_code = auth_group.group_code 
	join	stp_auth_staff auth_staff on auth_staff.auth_dep_code = auth_group.auth_group_code 
	join	staff s on s.staff_code = auth_staff.staff_code
	join	stp_sub_menu as ssm on ssm.sub_menu_code = auth.sub_menu_code
	join	stp_menu_system as sms on sms.menu_system_code = ssm.menu_system_code
	join	stp_main_system as smas on smas.main_system_code = sms.main_system_code
--	join	master_type as mt on mt.type_code = ssm.type_code
	where	auth.department_code in  (select department_code from @table where department_code is not null) and
			@group_code in (0, auth_group.group_code ) and
			@staff_code in (0, auth_staff.staff_code) and
			@auth_staff_status in (0, auth_staff.auth_staff_status ) and
			@main_system_code in (0,sms.main_system_code) and
			@menu_system_code in (0,ssm.menu_system_code)   
			--and	@department_code in (0,dept.department_code)
						
	group by auth_staff.staff_code, s.staff_firstname, s.staff_lastname, 
				auth_group.group_code, g.group_user_name, dept.department_code , auth.department_code,
				dept.department_name ,auth_group.auth_group_status, dept.department_parent, 
				mas.master_department_level, sms.main_system_code, smas.main_system_name,auth_staff.auth_staff_status,
				ssm.menu_system_code, sms.menu_system_name--, auth.sub_menu_code-- , ssm.sub_menu_name--,mt.[type_name]
	order by parentName1,parentName2 ,parentName3,parentName4,parentName5
END

GO
